package weeklyreport

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.CellStyle
import org.apache.poi.ss.usermodel.DataFormat
import org.apache.poi.ss.usermodel.DateUtil
import org.apache.poi.ss.usermodel.Font
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.util.CellRangeAddress
import org.apache.poi.xssf.usermodel.XSSFWorkbook

class ExcelUtil {
	private static final def TASK_SHEET_NAME = "问题清单开发跟踪"
	private static final def ISSUE_SHEET_NAME = "ITSM平台问题记录"
	private static final def WORKLOAD_SHEET_NAME = "工作量统计"
	private static final def PERFORMANCE_SHEET_NAME = "绩效统计"
	private static final def TASK_PROPS = [
		"issueNumber",
		"smNumber",
		"reqEmpNumber",
		"devEmpNumber",
		"crNumber",
		"groupName",
		"itsmNumber",
		"status",
		"lateState",
		"planningBegin",
		"beginDate",
		"planningEnd",
		"endDate",
		"planningClose",
		"closeDate",
		"internalVersion",
		"internalTest",
		"needTS",
		"company",
		"name",
		"consultant",
		"tester",
		"reqName",
		"description",
		"remark",
		"manDay",
		"planningManDay"
	]

	private static final def ISSUE_PROPS = [
		"date",
		"imNumber",
		"depart",
		"name",
		"description",
		"reason",
		"status"
	]

	private static final def TASKTABLE_PROPS = [
		"year",
		"month",
		"taskAmount",
		"manDay",
		"planningManDay"
	]
	
	private static final def ISSUETABLE_PROPS = [
		"year",
		"month",
		"amount"
	]
	
	private static final def DEVTABLE_PROPS = [
		"year",
		"developer",
		"amount",
		"waiting",
		"developing",
		"complete"	
	]

	private static final def PERFORMANCE_PROPS = [
		"date",
		"year",
		"weekOfYear",
		"complete",
		"developing",
		"waiting"
	]

	private static final def TASK_STATUS = [
		"完成" : "complete",
		"开发中" : "developing",
		"待开发" : "waiting"
	]

	private static final def WORKLOAD_TASKTABLE_BEGINROW = 20
	private static final def WORKLOAD_TASKTABLE_BEGINCOL = 0
	private static final def WORKLOAD_ISSUETABLE_BEGINROW = 20
	private static final def WORKLOAD_ISSUETABLE_BEGINCOL = 10
	private static final def WORKLOAD_DEVTABLE_BEGINROW = 20
	private static final def WORKLOAD_DEVTABLE_BEGINCOL = 20

	private static Font font
	private static CellStyle defaultStyle
	private static CellStyle dateStyle
	private static CellStyle regionStyle


	public static void read(InputStream is) {
		Workbook wb = new XSSFWorkbook(is)
		List<Task> tasks = getTaskSheet(wb)
		List<Issue> issues = getIssueSheet(wb)
		tasks.each {
			it.save()
			if(it.hasErrors()) {
				println it.errors
			}
		}
		issues.each {
			it.save()
			if(it.hasErrors()) {
				println it.errors
			}
		}
	}

	public static Workbook write(File file) {
		List<Task> tasks = Task.getAll()
		List<Issue> issues = Issue.getAll()
		InputStream is = new FileInputStream(file)
		Workbook wb = new XSSFWorkbook(is)
		font = createFont(wb)
		dateStyle = createDateStyle(wb)
		defaultStyle = createDefaultStyle(wb)
		regionStyle = createRegionStyle(wb)
		setTaskSheet(tasks, wb)
		setIssueSheet(issues, wb)
		setWorkloadSheet(tasks, issues, wb)
		setPerformanceSheet(tasks, wb)
		return wb
	}

	private static List<Task> getTaskSheet(Workbook wb) {
		List<Task> tasks = []
		Sheet sheet = wb.getSheet(TASK_SHEET_NAME)
		int lastRowNum = sheet.getLastRowNum()
		for(int i = 1; i <= lastRowNum; i++) {
			Task task = new Task()
			Row row = sheet.getRow(i)
			TASK_PROPS.eachWithIndex {prop, j ->
				Cell cell = row.getCell(j)
				def value
				switch(cell.getCellType()) {
					case Cell.CELL_TYPE_NUMERIC :
						if(DateUtil.isCellDateFormatted(cell)) {
							value = cell.getDateCellValue()
						} else {
							value = cell.getNumericCellValue().toBigDecimal()
						}
						break
					case Cell.CELL_TYPE_STRING:
						if(cell.getStringCellValue().trim().equals("")) {
							value = null
						} else {
							value = cell.getStringCellValue().trim()
						}
						break
					case Cell.CELL_TYPE_BLANK:
						value = null
						break
					default:
						value = null
						break
				}

				if(prop.equals("issueNumber") && value == null) {
					def now = Calendar.getInstance().format("yyMMdd")
					value = "任务${now + i}"
				}
				if((prop.equals("manDay") || prop.equals("planningManDay")) && value != null ) {
					if(value.equals("")) {
						value = null
					} else {
						value = new BigDecimal(value)
					}
				}

				if(prop.equals("name")) {
					User user = User.findByName(value)
					if(!user) {
						def company = row.getCell(j - 1).getStringCellValue()
						user = new User(name:value, company:company, role:"user")
						user.save()
						if(user.hasErrors()) {
							println user.errors
						}
					}
					task.setDeveloper(user)
				} else if(prop.equals("company")) {
				} else {
					task.setProperty(prop, value)
				}
			}
			tasks.add(task)
		}
		return tasks
	}

	private static List<Issue> getIssueSheet(Workbook wb) {
		List<Issue> issues = []
		Sheet sheet = wb.getSheet(ISSUE_SHEET_NAME)
		int lastRowNum = sheet.getLastRowNum()
		for(int i = 1; i <= lastRowNum; i++) {
			Issue issue = new Issue()
			Row row = sheet.getRow(i)
			ISSUE_PROPS.eachWithIndex {prop, j ->
				Cell cell = row.getCell(j)
				def value
				switch(cell.getCellType()) {
					case Cell.CELL_TYPE_NUMERIC :
						if(DateUtil.isCellDateFormatted(cell)) {
							value = cell.getDateCellValue()
						} else {
							value = cell.getNumericCellValue().toBigDecimal()
						}
						break
					case Cell.CELL_TYPE_STRING:
						if(cell.getStringCellValue().trim().equals("")) {
							value = null
						} else {
							value = cell.getStringCellValue().trim()
						}
						break
					case Cell.CELL_TYPE_BLANK:
						value = null
						break
					default:
						value = null
						break
				}
				if(prop.equals("name")) {
					User user = User.findByName(value)
					if(!user) {
						def company = row.getCell(j - 1).getStringCellValue()
						user = new User(name:value, company:company, role:"user")
						user.save()
						if(user.hasErrors()) {
							println user.errors
						}
					}
					issue.setSolveMan(user)
				} else {
					issue.setProperty(prop, value)
				}
			}
			issues.add(issue)
		}
		return issues
	}

	private static void setTaskSheet(List<Task> tasks, Workbook wb) {
		Sheet sheet = wb.getSheet(TASK_SHEET_NAME)
		tasks.eachWithIndex {task, i ->
			User user = task.getDeveloper()
			Row row = sheet.createRow(i + 1)
			TASK_PROPS.eachWithIndex {prop, j ->
				Cell cell = row.createCell(j)
				def value
				if(prop.equals("company") || prop.equals("name")) {
					value = user.getProperty(prop)
				}else {
					value = task.getProperty(prop)
				}
				cell.setCellValue(value)
				if(value instanceof Date) {
					cell.setCellStyle(dateStyle)
				} else {
					cell.setCellStyle(defaultStyle)
				}
			}
		}
	}

	private static void setIssueSheet(List<Issue> issues, Workbook wb) {
		Sheet sheet = wb.getSheet(ISSUE_SHEET_NAME)
		issues.eachWithIndex {issue, i ->
			User user = issue.getSolveMan()
			Row row = sheet.createRow(i + 1)
			ISSUE_PROPS.eachWithIndex {prop, j ->
				Cell cell = row.createCell(j)
				def value
				if(prop.equals("name")) {
					value = user.getProperty(prop)
				} else {
					value = issue.getProperty(prop)
				}
				cell.setCellValue(value)
				if(value instanceof Date) {
					cell.setCellStyle(dateStyle)
				} else {
					cell.setCellStyle(defaultStyle)
				}
			}
		}
	}

	private static void setWorkloadSheet(List<Task> tasks, List<Issue> issues, Workbook wb) {
		Sheet sheet = wb.getSheet(WORKLOAD_SHEET_NAME)
		setTaskTables(tasks, sheet)
		setIssueTables(issues, sheet)
		setDevTables(tasks, sheet)
	}

	private static void setPerformanceSheet(List<Task> tasks, Workbook wb) {
		Map<Date, Week> weeks = getWeeks(tasks)
		Sheet sheet = wb.getSheet(PERFORMANCE_SHEET_NAME)
		weeks.eachWithIndex {it, i ->
			Week week = it.value
			Row row = sheet.createRow(i + 1)
			PERFORMANCE_PROPS.eachWithIndex {prop, j ->
				Cell cell = row.createCell(j)
				def value
				value = week.getProperty(prop)
				if(value != 0) {
					cell.setCellValue(value)
				}
				if(value instanceof Date) {
					cell.setCellStyle(dateStyle)
				} else {
					cell.setCellStyle(defaultStyle)
				}
			}
		}
	}

	private static Map<Integer, Map<Integer, TaskTable>> getTaskTables(List<Task> tasks) {
		Map<Integer, Map<Integer, TaskTable>> taskTables = new TreeMap<Integer, Map<Integer, TaskTable>>()
		tasks.each {
			Date planningBegin = it.getPlanningBegin()
			if(planningBegin) {
				Calendar calendar = Calendar.getInstance()
				calendar.setTime(planningBegin)
				def year = calendar.get(Calendar.YEAR)
				def month = calendar.get(Calendar.MONTH) + 1

				Map<Integer, TaskTable> taskTablesOfYear = taskTables.get(year)
				if(!taskTablesOfYear) {
					taskTablesOfYear = new TreeMap<Integer, TaskTable>()
					taskTables.put(year, taskTablesOfYear)
				}
				TaskTable taskTable = taskTablesOfYear.get(month)
				if(!taskTable) {
					taskTable = new TaskTable("${year}年", "${month}月")
					taskTablesOfYear.put(month, taskTable)
				}
				taskTable.addTask()
				taskTable.addManDay(it.getManDay())
				taskTable.addPlanningManDay(it.getPlanningManDay())
			}
		}
		return taskTables
	}
	
	private static Map<Integer, Map<Integer, IssueTable>> getIssueTables(List<Issue> issues) {
		Map<Integer, Map<Integer, IssueTable>> issueTables = new TreeMap<Integer, Map<Integer, IssueTable>>()
		issues.each {
			Date date = it.getDate()
			if(date) {
				Calendar calendar = Calendar.getInstance()
				calendar.setTime(date)
				def year = calendar.get(Calendar.YEAR)
				def month = calendar.get(Calendar.MONTH) + 1
				
				Map<Integer, IssueTable> issueTablesOfYear = issueTables.get(year)
				if(!issueTablesOfYear) {
					issueTablesOfYear = new TreeMap<Integer, IssueTable>()
					issueTables.put(year, issueTablesOfYear)
				}
				IssueTable issueTable = issueTablesOfYear.get(month)
				if(!issueTable) {
					issueTable = new IssueTable("${year}年", "${month}月")
					issueTablesOfYear.put(month, issueTable)
				}
				issueTable.add();
			}
		}
		return issueTables
	}
	
	private static Map<Integer, Map<String, DevTable>> getDevTables(List<Task> tasks) {
		Map<Integer, Map<String, DevTable>> devTables = new TreeMap<Integer, Map<String, DevTable>>()
		tasks.each {
			Date date = it.getPlanningBegin()
			if(date) {
				Calendar calendar = Calendar.getInstance()
				calendar.setTime(date)
				def year = calendar.get(Calendar.YEAR)
				def developer = it.getDeveloper().getName()
				Map<String, DevTable> devTablesOfYear = devTables.get(year)
				if(!devTablesOfYear) {
					devTablesOfYear = new TreeMap<String, DevTable>()
					devTables.put(year, devTablesOfYear)
				}
				DevTable devTable = devTablesOfYear.get(developer)
				if(!devTable) {
					devTable = new DevTable("${year}年", developer)
					devTablesOfYear.put(developer, devTable)
				}
				def status = TASK_STATUS.getAt(it.getStatus())
				devTable.add(status)
			}
		}
		return devTables
	}
	
	private static Map<Date, Week> getWeeks(List<Task> tasks) {
		Map<Date, Week> weeks = new TreeMap<Date, Week>()
		tasks.each {
			Date planningBegin = it.getPlanningBegin()
			if(planningBegin) {
				Calendar calendar = Calendar.getInstance()
				calendar.setTime(planningBegin)
				calendar.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY)
				Date date = calendar.getTime()
				String year = calendar.get(Calendar.YEAR)
				String weekOfYear = "${calendar.get(Calendar.WEEK_OF_YEAR)}周"
				String status = TASK_STATUS.getAt(it.getStatus())
	
				Week week = weeks.get(date)
				if(!week) {
					week = new Week()
					weeks.put(date, week)
				}
	
				week.setProperty("date", date)
				week.setProperty("year", year)
				week.setProperty("weekOfYear", weekOfYear)
				week.add(status)
			}
		}
		return weeks
	}

	private static void setTaskTables(List<Task> tasks, Sheet sheet) {
		Map<Integer, Map<Integer, TaskTable>> taskTables = getTaskTables(tasks)
		def beginRow = WORKLOAD_TASKTABLE_BEGINROW + 1
		def beginCol = WORKLOAD_TASKTABLE_BEGINCOL
		taskTables.eachWithIndex {it, x ->
			Map<Integer, TaskTable> taskTablesOfYear = it.value
			taskTablesOfYear.eachWithIndex {item, y ->
				TaskTable taskTable = item.value
				Row row = sheet.getRow(y + beginRow)
				if(!row) {
					row = sheet.createRow(y + beginRow)
				}
				
				TASKTABLE_PROPS.eachWithIndex {prop, z ->
					Cell cell = row.createCell(z + beginCol)
					cell.setCellStyle(defaultStyle)
					if(z == 0 && y == 0) {
						cell.setCellValue(taskTable.getProperty(prop))
						cell.setCellStyle(regionStyle)
					} else if(z > 0) {
						cell.setCellValue(taskTable.getProperty(prop))
					}
				}
			}
			def length = taskTablesOfYear.size()
			sheet.addMergedRegion(new CellRangeAddress(beginRow, beginRow + length - 1, beginCol, beginCol))
			beginRow += length
		}
	}
	
	private static void setIssueTables(List<Issue> issues, Sheet sheet) {
		Map<Integer, Map<Integer, IssueTable>> issueTables = getIssueTables(issues)
		def beginRow = WORKLOAD_ISSUETABLE_BEGINROW + 1
		def beginCol = WORKLOAD_ISSUETABLE_BEGINCOL
		issueTables.eachWithIndex {it, x ->
			Map<Integer, IssueTable> issueTablesOfYear = it.value
			issueTablesOfYear.eachWithIndex {item, y ->
				IssueTable issueTable = item.value
				Row row = sheet.getRow(y + beginRow)
				if(!row) {
					row = sheet.createRow(y + beginRow)
				}
				
				ISSUETABLE_PROPS.eachWithIndex {prop, z ->
					Cell cell = row.createCell(z + beginCol)
					cell.setCellStyle(defaultStyle)
					if(z == 0 && y == 0) {
						cell.setCellValue(issueTable.getProperty(prop))
						cell.setCellStyle(regionStyle)
					} else if(z > 0) {
						cell.setCellValue(issueTable.getProperty(prop))
					}
				}
			}
			def length = issueTablesOfYear.size()
			sheet.addMergedRegion(new CellRangeAddress(beginRow, beginRow + length - 1, beginCol, beginCol))
			beginRow += length
		}
	}
	
	private static void setDevTables(List<Task> tasks, Sheet sheet) {
		Map<Integer, Map<String, DevTable>> devTables = getDevTables(tasks)
		def beginRow = WORKLOAD_DEVTABLE_BEGINROW + 1
		def beginCol = WORKLOAD_DEVTABLE_BEGINCOL
		devTables.eachWithIndex {it, x ->
			Map<String, DevTable> devTablesOfYear = it.value
			devTablesOfYear.eachWithIndex {item, y ->
				DevTable devTable = item.value
				Row row = sheet.getRow(y + beginRow)
				if(!row) {
					row = sheet.createRow(y + beginRow)
				}
				
				DEVTABLE_PROPS.eachWithIndex {prop, z ->
					Cell cell = row.createCell(z + beginCol)
					cell.setCellStyle(defaultStyle)
					if(z == 0 && y == 0) {
						cell.setCellValue(devTable.getProperty(prop))
						cell.setCellStyle(regionStyle)
					} else if(z > 0) {
						cell.setCellValue(devTable.getProperty(prop))
					}
				}
			}
			def length = devTablesOfYear.size()
			sheet.addMergedRegion(new CellRangeAddress(beginRow, beginRow + length - 1, beginCol, beginCol))
			beginRow += length
		}
	}

	private static Font createFont(Workbook wb) {
		Font font = wb.createFont()
		font.setFontName("微软雅黑")
		font.setFontHeight(8)
		return font
	}

	private static CellStyle createDateStyle(Workbook wb) {
		DataFormat df = wb.createDataFormat()
		CellStyle style = createDefaultStyle(wb)
		style.setDataFormat(df.getFormat("yyyy/mm/dd"))
		return style
	}

	private static CellStyle createDefaultStyle(Workbook wb) {
		CellStyle style = wb.createCellStyle()
		style.setBorderBottom(CellStyle.BORDER_THIN)
		style.setBorderLeft(CellStyle.BORDER_THIN)
		style.setBorderRight(CellStyle.BORDER_THIN)
		style.setBorderTop(CellStyle.BORDER_THIN)
		style.setFont(font)
		return style
	}

	private static CellStyle createRegionStyle(Workbook wb) {
		CellStyle style = wb.createCellStyle()
		style.setBorderBottom(CellStyle.BORDER_THIN)
		style.setBorderLeft(CellStyle.BORDER_THIN)
		style.setBorderRight(CellStyle.BORDER_THIN)
		style.setBorderTop(CellStyle.BORDER_THIN)
		style.setFont(font)
		style.setAlignment(CellStyle.ALIGN_CENTER)
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER)
		return style
	}

	public static void main(String[] args) {
	}
}
